package com.hex.ds.hdrs.common.excel;

import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelHelper {

	private static Logger logger = LoggerFactory.getLogger(ExcelHelper.class);

	private static final int version2003 = 2003;

	private static final int version2007 = 2007;

	private static int version = version2003;

	private String COLUMN_DATA_KEY = "column";

	// excel文件路径
	private String filePath;
	// 开始头行号
	private int startHeadRow = 0;
	// 数据开始行号
	private int startDataRow = 1;
	// sheet的编号
	private int sheetNum = 0;
	// excel总行记录
	private int totalRows = 0;
	private Workbook wb = null;
	// 总多少列
	private int cellCols = 0;

	private Sheet currentSheet;

	/**
	 * 获取总记录条数
	 * @return int
	 * */
	public int getDataRows() {
		return this.totalRows - startDataRow;
	}

	private int version() {
		//文件后缀是否是 ".xls"
		if (this.filePath.endsWith(".xls")) {
			version = version2003;
		} else if (this.filePath.endsWith(".xlsx")) {
			version = version2007;
		}
		return version;
	}

	public int getTotalRows() {
		return totalRows;
	}

	public void setTotalRows(int totalRows) {
		this.totalRows = totalRows;
	}

	public int getSheetNum() {
		return sheetNum;
	}

	public void setSheetNum(int sheetNum) {
		this.sheetNum = sheetNum;
	}

	public int getStartDataRow() {
		return startDataRow;
	}

	public void setStartDataRow(int startDataRow) {
		this.startDataRow = startDataRow;
	}

	public String getFilePath() {
		return filePath;
	}

	public void setFilePath(String filePath) {
		this.filePath = filePath;
	}

	public int getStartHeadRow() {
		return startHeadRow;
	}

	public void setStartHeadRow(int startHeadRow) {
		this.startHeadRow = startHeadRow;
	}

	public ExcelHelper(int startHeadRow, String filePath) {
		this.startHeadRow = startHeadRow;
		this.filePath = filePath;
		init();
	}

	public void setPreKey(String key) {
		COLUMN_DATA_KEY = key;
	}

	public ExcelHelper(String filePath) {
		this.filePath = filePath;
		init();
	}

	public Workbook getWb() {
		return wb;
	}

	public void setWb(Workbook wb) {
		this.wb = wb;
	}

	@Override
	public String toString() {
		return "ExcelHelper [wb=" + wb + "]";
	}

	// 初始化
	private void init() {
		version();
		readExcel();
	}

	@SuppressWarnings(value = { "unchecked", "deprecation" })
	private void readExcel() {
		InputStream stream = null;
		try { 
			if (version == version2003) {
				stream = new FileInputStream(this.filePath);
				wb = (Workbook) new HSSFWorkbook(stream);
			} else if (version == version2007) {
				wb = (Workbook) new XSSFWorkbook(this.filePath);
			}
			currentSheet = getSheet();
		} catch (Exception e) {

		}finally{
			IOUtils.closeQuietly(stream);
		}
	}

	//

	/**
	 * 获取指定sheet
	 * @return Sheet
	 * */
	private Sheet getSheet() {
		Sheet sheet = wb.getSheetAt(this.sheetNum);

		this.totalRows = sheet.getLastRowNum();
		this.cellCols = sheet.getRow(0).getPhysicalNumberOfCells();
		return sheet;
	}

	/**
	 * 获取指定行号数据
	 * */
	public Row getRows(int row) {
		return this.currentSheet.getRow(row);
	}

	/**
	 * 获得返回数据值得Key
	 * @param columnNum
	 * */
	private String getKey(int columnNum) {
		return COLUMN_DATA_KEY + columnNum;
	}

	/**
	 * 获取某一行数据
	 * 
	 * @param rowIndex
	 *            计数从0开始，rowIndex为0代表header行
	 * @return
	 */
	public String[] getRowData(int sheetIndex, int rowIndex) {
		String[] dataArray = null;
		if (rowIndex > this.getColumnNum(sheetIndex)) {
			return dataArray;
		} else {
			dataArray = new String[this.getColumnNum(sheetIndex)];
			return dataArray;
		}

	}

	/**
	 * 需要指定不同sheet获得列记录数
	 * @param sheetIndex
	 * @return int
	 * */
	public int getColumnNum(int sheetIndex) {
		Sheet sheet = this.wb.getSheetAt(sheetIndex);
		Row row = sheet.getRow(0);
		if (row != null && row.getLastCellNum() > 0) {
			return row.getLastCellNum();
		}
		return 0;
	}

	/**
	 * 返回Excel最大行index值，实际行数要加1
	 * 
	 * @return
	 */
	public int getRowNum(int sheetIndex) {
		Sheet sheet = wb.getSheetAt(sheetIndex);
		return sheet.getLastRowNum();
	}

}
